package by.bsu.rfe.belianko.euniversity.dao;

import java.io.UnsupportedEncodingException;
import java.security.NoSuchAlgorithmException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Savepoint;
import java.util.ArrayList;
import java.util.List;
import java.security.MessageDigest;

import org.apache.log4j.Logger;
import by.bsu.rfe.belianko.euniversity.constants.ErrorMessages;
import by.bsu.rfe.belianko.euniversity.constants.LoggerMessages;
import by.bsu.rfe.belianko.euniversity.constants.SQLQueries;
import by.bsu.rfe.belianko.euniversity.exceptions.ConnectionExceptions;
import by.bsu.rfe.belianko.euniversity.exceptions.DaoTechnicalException;
import by.bsu.rfe.belianko.euniversity.model.Faculty;
import by.bsu.rfe.belianko.euniversity.model.Student;
import by.bsu.rfe.belianko.euniversity.pool.ConnectionPool;

/**
 * Load students from DB using pool of JDBC connections.
 * 
 * @author Vitali_Belianko
 * 
 */

public final class StudentsDAO implements IStudentsDAO {
	private static final String ENCODING = "UTF-8";
	private static final String MD5 = "MD5";
	private static final String POWER_OF_USERS_IS = "Power of users is ";
	private static final String CREATED_LIST_OF_ACCEPTED_STUDENTS = "Created list of accepted students";
	private static final String CREATED_LIST_OF_NOT_APPROVED_STUDENTS = "Created list of not approved students";
	private static final String CREATED_STUDENT = "Created student";
	private static final String STUDENT_WITH_ID = "Student with id: ";
	private static final String COMMIT = "Commit!";
	private static final String CREATED_SAVEPOINT = "Created savepoint";
	private static final String PASSWORD = "password";
	private static final String CAN_NOT_ROLLBACK = "Can not rollback";
	private static final String CAN_NOT_SET_AUTOCOMMIT = "Can not set autocommit";
	private static final String POWER = "power";
	private static final String ACCEPTED = "accepted";
	private static final String APPROVED = "approved";
	private static final String FACULTY_NAME = "faculty_name";
	private static final String FACULTY_ID = "faculty_id";
	private static final String TOTAL_SCORE = "total_score";
	private static final String NAME = "name";
	private static final String ID = "id";
	private static final Logger LOGGER = Logger.getLogger(StudentsDAO.class);
	private static ConnectionPool connectionPool;
	private static StudentsDAO instance;

	public static StudentsDAO getInstance() {
		if (instance == null) {
			instance = new StudentsDAO();
		}
		return instance;
	}

	private StudentsDAO() {

	}

	/**
	 * Saves the student in the database.
	 * 
	 * @param student
	 * 
	 * @return null or error message if the student exists
	 * 
	 * @throws DaoTechnicalException
	 */
	@Override
	public String saveStudent(Student student) throws DaoTechnicalException {

		try {
			connectionPool = ConnectionPool.getInstance();
		} catch (ConnectionExceptions e) {
			LOGGER.error(e.getMessage());
			throw new DaoTechnicalException(ErrorMessages.CAN_NOT_GET_ACCESS_TO_CONNECTION_POOL, e);

		}
		if (checkStudent(student.getLogin()) == null) {
			Connection connection;
			try {
				connection = connectionPool.getConnection();
			} catch (ConnectionExceptions e) {
				LOGGER.error(e.getMessage());
				throw new DaoTechnicalException(ErrorMessages.CAN_NOT_GET_CONNECTION, e);
			}

			LOGGER.debug(LoggerMessages.RECEIVE_CONNECTION);
			String userPassword = generateMD5(student.getPassword());
			PreparedStatement addUserStatement = null;
			PreparedStatement addStudentStatement = null;
			Savepoint savepoint = null;

			try {
				connection.setAutoCommit(false);
				addUserStatement = connection.prepareStatement(SQLQueries.ADD_USER);
				addStudentStatement = connection.prepareStatement(SQLQueries.ADD_STUDENT);
				savepoint = connection.setSavepoint();

				LOGGER.debug(CREATED_SAVEPOINT);

				addUserStatement.setString(1, student.getLogin());
				addUserStatement.setString(2, userPassword);
				addUserStatement.executeUpdate();
				addStudentStatement.setString(1, student.getLogin());
				addStudentStatement.setString(2, student.getName());
				addStudentStatement.executeUpdate();
				connection.commit();

				LOGGER.debug(COMMIT);

			} catch (SQLException e) {
				if (connection != null) {
					try {
						connection.rollback(savepoint);
					} catch (SQLException excep) {
						LOGGER.error(excep.getMessage());
						throw new DaoTechnicalException(CAN_NOT_ROLLBACK, excep);
					}
				}

				LOGGER.error(e.getMessage());
				throw new DaoTechnicalException(ErrorMessages.CAN_NOT_EXECUTE_QUERY, e);

			} finally {
				if (addUserStatement != null) {
					try {
						addUserStatement.close();
					} catch (SQLException e) {
						LOGGER.error(e.getMessage());
						throw new DaoTechnicalException(ErrorMessages.CAN_NOT_CLOSE_STATEMENT, e);
					}
				}
				if (addStudentStatement != null) {
					try {
						addStudentStatement.close();
					} catch (SQLException e) {
						LOGGER.error(e.getMessage());
						throw new DaoTechnicalException(ErrorMessages.CAN_NOT_CLOSE_STATEMENT, e);
					}
				}
				try {
					connection.setAutoCommit(true);
				} catch (SQLException e) {
					LOGGER.error(e.getMessage());
					throw new DaoTechnicalException(CAN_NOT_SET_AUTOCOMMIT, e);
				}
				connectionPool.releaseConnection(connection);
			}
			return null;

		} else {
			return ErrorMessages.USER_EXISTS;

		}

	}

	/**
	 * Looking for student in the DB with such login.
	 * 
	 * @param login
	 *            - input login
	 * 
	 * @return students id with such login or null if the student does not exist
	 * 
	 * @throws DaoTechnicalException
	 */
	private Long checkStudent(String login) throws DaoTechnicalException {

		try {
			connectionPool = ConnectionPool.getInstance();
		} catch (ConnectionExceptions e) {
			LOGGER.error(e.getMessage());
			throw new DaoTechnicalException(ErrorMessages.CAN_NOT_GET_ACCESS_TO_CONNECTION_POOL, e);
		}
		Connection connection;
		try {
			connection = connectionPool.getConnection();
		} catch (ConnectionExceptions e) {
			LOGGER.error(e.getMessage());
			throw new DaoTechnicalException(ErrorMessages.CAN_NOT_GET_CONNECTION, e);
		}

		LOGGER.debug(LoggerMessages.RECEIVE_CONNECTION);

		PreparedStatement statement = null;
		ResultSet resultSet = null;
		Long id = null;

		try {
			statement = connection.prepareStatement(SQLQueries.CHECK_STUDENT);

			LOGGER.debug(LoggerMessages.TRYING_EXECUTE_QUERY);

			statement.setString(1, login);

			try {
				resultSet = statement.executeQuery();

				LOGGER.debug(LoggerMessages.EXECUTE_QUERY);

				while (resultSet.next()) {
					try {
						id = resultSet.getLong(ID);

						LOGGER.debug(STUDENT_WITH_ID + id);

					} catch (SQLException e) {
						LOGGER.error(e.getMessage());
						throw new DaoTechnicalException(ErrorMessages.CAN_NOT_GET_CELL_DATA, e);
					}
				}

			} catch (SQLException e) {
				LOGGER.error(e.getMessage());
				throw new DaoTechnicalException(ErrorMessages.CAN_NOT_EXECUTE_QUERY, e);
			}

		} catch (SQLException e) {
			LOGGER.error(e.getMessage());
			throw new DaoTechnicalException(ErrorMessages.CAN_NOT_CREATE_STATEMENT, e);
		} finally {
			if (resultSet != null)
				try {
					resultSet.close();
				} catch (SQLException e) {
					LOGGER.error(e.getMessage());
					throw new DaoTechnicalException(ErrorMessages.CAN_NOT_CLOSE_RESULT_SET, e);
				}

			if (statement != null) {
				try {
					statement.close();
				} catch (SQLException e) {
					LOGGER.error(e.getMessage());
					throw new DaoTechnicalException(ErrorMessages.CAN_NOT_CLOSE_STATEMENT, e);
				}
			}
			connectionPool.releaseConnection(connection);
		}

		return id;
	}

	/**
	 * Creates student from the DB.
	 * 
	 * @param login
	 *            - input login
	 * 
	 * @return student with such login
	 * 
	 * @throws DaoTechnicalException
	 */
	@Override
	public Student getStudentByLogin(String login) throws DaoTechnicalException {
		try {
			connectionPool = ConnectionPool.getInstance();
		} catch (ConnectionExceptions e) {
			LOGGER.error(e.getMessage());
			throw new DaoTechnicalException(ErrorMessages.CAN_NOT_GET_ACCESS_TO_CONNECTION_POOL, e);
		}
		Connection connection = null;
		try {
			connection = connectionPool.getConnection();
		} catch (ConnectionExceptions ex) {
			LOGGER.error(ex.getMessage());
			throw new DaoTechnicalException(ErrorMessages.CAN_NOT_GET_CONNECTION, ex);
		}
		LOGGER.debug(LoggerMessages.RECEIVE_CONNECTION);
		LOGGER.debug(login);

		PreparedStatement statement = null;
		ResultSet resultSet = null;
		Student student = null;
		Faculty faculty = null;
		try {
			statement = connection.prepareStatement(SQLQueries.GET_STUDENT_BY_LOGIN);

			LOGGER.debug(LoggerMessages.TRYING_EXECUTE_QUERY);

			statement.setString(1, login);

			try {
				resultSet = statement.executeQuery();

				LOGGER.debug(LoggerMessages.EXECUTE_QUERY);

				while (resultSet.next()) {

					try {
						long id = resultSet.getLong(ID);
						String name = resultSet.getString(NAME);
						int totalScore = resultSet.getInt(TOTAL_SCORE);
						long facultyId = resultSet.getLong(FACULTY_ID);
						String approved = resultSet.getString(APPROVED);
						String accepted = resultSet.getString(ACCEPTED);
						student = new Student();
						faculty = new Faculty();
						faculty.setFacultyId(facultyId);
						student.setId(id);
						student.setName(name);
						student.setApproved(approved);
						student.setAccepted(accepted);
						student.setTotalScore(totalScore);
						student.setFaculty(faculty);

						LOGGER.debug(CREATED_STUDENT);

					} catch (SQLException e) {
						LOGGER.error(e.getMessage());
						throw new DaoTechnicalException(ErrorMessages.CAN_NOT_GET_CELL_DATA, e);
					}
				}

			} catch (SQLException e) {
				LOGGER.error(e.getMessage());
				throw new DaoTechnicalException(ErrorMessages.CAN_NOT_EXECUTE_QUERY, e);
			}

		} catch (SQLException e) {
			LOGGER.error(e.getMessage());
			throw new DaoTechnicalException(ErrorMessages.CAN_NOT_CREATE_STATEMENT, e);

		} finally {
			if (resultSet != null)
				try {
					resultSet.close();
				} catch (SQLException e) {
					LOGGER.error(e.getMessage());
					throw new DaoTechnicalException(ErrorMessages.CAN_NOT_CLOSE_RESULT_SET, e);
				}

			if (statement != null) {
				try {
					statement.close();
				} catch (SQLException e) {
					LOGGER.error(e.getMessage());
					throw new DaoTechnicalException(ErrorMessages.CAN_NOT_CLOSE_STATEMENT, e);
				}
			}
			connectionPool.releaseConnection(connection);
		}

		return student;
	}

	/**
	 * Enrolls the student on the faculty.
	 * 
	 * @param facultyId
	 *            - id of faculty where enrolled the student
	 * @param id
	 *            - id of the current student
	 * @param totalScore
	 *            - total score of the current student
	 * 
	 * @throws DaoTechnicalException
	 */
	@Override
	public void enrollInFaculty(long facultyId, long id, int totalScore) throws DaoTechnicalException {
		try {
			connectionPool = ConnectionPool.getInstance();
		} catch (ConnectionExceptions e) {
			LOGGER.error(e.getMessage());
			throw new DaoTechnicalException(ErrorMessages.CAN_NOT_GET_ACCESS_TO_CONNECTION_POOL, e);

		}
		Connection connection;
		try {
			connection = connectionPool.getConnection();
		} catch (ConnectionExceptions ex) {
			LOGGER.error(ex.getMessage());
			throw new DaoTechnicalException(ErrorMessages.CAN_NOT_GET_CONNECTION, ex);

		}

		LOGGER.debug(LoggerMessages.RECEIVE_CONNECTION);

		PreparedStatement statement = null;

		try {
			statement = connection.prepareStatement(SQLQueries.ENROLL_ON_FACULTY);

			LOGGER.debug(LoggerMessages.TRYING_EXECUTE_QUERY);

			statement.setLong(1, facultyId);
			statement.setInt(2, totalScore);
			statement.setLong(3, id);

			try {
				statement.executeUpdate();

				LOGGER.debug(LoggerMessages.EXECUTE_QUERY);

			} catch (SQLException e) {
				LOGGER.error(e.getMessage());
				throw new DaoTechnicalException(ErrorMessages.CAN_NOT_EXECUTE_QUERY, e);
			}
		} catch (SQLException e) {
			LOGGER.error(e.getMessage());
			throw new DaoTechnicalException(ErrorMessages.CAN_NOT_CREATE_STATEMENT, e);
		} finally {
			if (statement != null) {
				try {
					statement.close();
				} catch (SQLException e) {
					LOGGER.error(e.getMessage());
					throw new DaoTechnicalException(ErrorMessages.CAN_NOT_CLOSE_STATEMENT, e);
				}
			}
			connectionPool.releaseConnection(connection);
		}

	}

	/**
	 * Creates list of students who enrolled to the faculty, but not approved by
	 * administrator.
	 * 
	 * @return list of not approved students
	 * 
	 * @throws DaoTechnicalException
	 */
	@Override
	public List<Student> getNotApprovedStudents() throws DaoTechnicalException {
		try {
			connectionPool = ConnectionPool.getInstance();
		} catch (ConnectionExceptions e) {
			LOGGER.error(e.getMessage());
			throw new DaoTechnicalException(ErrorMessages.CAN_NOT_GET_ACCESS_TO_CONNECTION_POOL, e);

		}
		Connection connection;
		try {
			connection = connectionPool.getConnection();
		} catch (ConnectionExceptions ex) {
			LOGGER.error(ex.getMessage());
			throw new DaoTechnicalException(ErrorMessages.CAN_NOT_GET_CONNECTION, ex);
		}

		LOGGER.debug(LoggerMessages.RECEIVE_CONNECTION);

		PreparedStatement statement = null;
		ResultSet resultSet = null;
		List<Student> notApprovedStudentsList = new ArrayList<Student>();
		Student student = null;
		Faculty faculty = null;

		notApprovedStudentsList = new ArrayList<Student>();

		try {
			statement = connection.prepareStatement(SQLQueries.GET_NOT_APPROVED_STUDENTS);

			LOGGER.debug(LoggerMessages.TRYING_EXECUTE_QUERY);

			try {
				resultSet = statement.executeQuery();

				LOGGER.debug(LoggerMessages.EXECUTE_QUERY);

				while (resultSet.next()) {

					try {
						String name = resultSet.getString(NAME);
						int totalScore = resultSet.getInt(TOTAL_SCORE);
						String facultyName = resultSet.getString(FACULTY_NAME);
						String approved = resultSet.getString(APPROVED);
						long id = resultSet.getLong(ID);

						student = new Student();
						faculty = new Faculty();

						faculty.setFacultyName(facultyName);
						student.setId(id);
						student.setName(name);
						student.setTotalScore(totalScore);
						student.setFaculty(faculty);
						student.setApproved(approved);
						notApprovedStudentsList.add(student);

					} catch (SQLException e) {
						LOGGER.error(e.getMessage());
						throw new DaoTechnicalException(ErrorMessages.CAN_NOT_GET_CELL_DATA, e);
					}
				}

				LOGGER.debug(CREATED_LIST_OF_NOT_APPROVED_STUDENTS);

			} catch (SQLException e) {
				LOGGER.error(e.getMessage());
				throw new DaoTechnicalException(ErrorMessages.CAN_NOT_EXECUTE_QUERY, e);
			}
		} catch (SQLException e) {
			LOGGER.error(e.getMessage());
			throw new DaoTechnicalException(ErrorMessages.CAN_NOT_CREATE_STATEMENT, e);
		} finally {
			if (resultSet != null)
				try {
					resultSet.close();
				} catch (SQLException e) {
					LOGGER.error(e.getMessage());
					throw new DaoTechnicalException(ErrorMessages.CAN_NOT_CLOSE_RESULT_SET, e);
				}
			if (statement != null) {
				try {
					statement.close();
				} catch (SQLException e) {
					LOGGER.error(e.getMessage());
					throw new DaoTechnicalException(ErrorMessages.CAN_NOT_CLOSE_STATEMENT, e);
				}
			}
			connectionPool.releaseConnection(connection);
		}

		return notApprovedStudentsList;
	}

	/**
	 * Approves students to the choosing faculty.
	 * 
	 * @param idArray
	 *            - array of students id where administrator marked checkbox
	 * 
	 * @throws DaoTechnicalException
	 */

	@Override
	public void approveStudents(long[] idArray) throws DaoTechnicalException {
		try {
			connectionPool = ConnectionPool.getInstance();
		} catch (ConnectionExceptions e) {
			LOGGER.error(e.getMessage());
			throw new DaoTechnicalException(ErrorMessages.CAN_NOT_GET_ACCESS_TO_CONNECTION_POOL, e);

		}
		Connection connection = null;
		try {
			connection = connectionPool.getConnection();
		} catch (ConnectionExceptions ex) {
			LOGGER.error(ex.getMessage());
			throw new DaoTechnicalException(ErrorMessages.CAN_NOT_GET_CONNECTION, ex);
		}

		LOGGER.debug(LoggerMessages.RECEIVE_CONNECTION);

		PreparedStatement approveStudentStatement = null;
		PreparedStatement addToSubmittedStatement = null;
		Savepoint savepoint = null;

		try {
			connection.setAutoCommit(false);
			approveStudentStatement = connection.prepareStatement(SQLQueries.APPROVE_STUDENT);
			addToSubmittedStatement = connection.prepareStatement(SQLQueries.ADD_TO_SUBMITTED);
			savepoint = connection.setSavepoint();

			for (long id : idArray) {
				approveStudentStatement.setLong(1, id);
				approveStudentStatement.executeUpdate();
				addToSubmittedStatement.setLong(1, id);
				addToSubmittedStatement.executeUpdate();
				connection.commit();
			}

		} catch (SQLException e) {

			if (connection != null) {
				try {
					connection.rollback(savepoint);
				} catch (SQLException excep) {
					LOGGER.error(excep.getMessage());
					throw new DaoTechnicalException(CAN_NOT_ROLLBACK, excep);
				}
			}
			LOGGER.error(e.getMessage());
			throw new DaoTechnicalException(ErrorMessages.CAN_NOT_EXECUTE_QUERY, e);
		} finally {
			if (approveStudentStatement != null) {
				try {
					approveStudentStatement.close();
				} catch (SQLException e) {
					LOGGER.error(e.getMessage());
					throw new DaoTechnicalException(ErrorMessages.CAN_NOT_CLOSE_STATEMENT, e);
				}
				if (addToSubmittedStatement != null) {
					try {
						addToSubmittedStatement.close();
					} catch (SQLException e) {
						LOGGER.error(e.getMessage());
						throw new DaoTechnicalException(ErrorMessages.CAN_NOT_CLOSE_STATEMENT, e);
					}
				}
				try {
					connection.setAutoCommit(true);
				} catch (SQLException e) {
					LOGGER.error(e.getMessage());
					throw new DaoTechnicalException(CAN_NOT_SET_AUTOCOMMIT, e);

				}
				connectionPool.releaseConnection(connection);
			}
		}

	}

	/**
	 * Creates the list of students who entered to the University.
	 * 
	 * @param facultyId
	 * 
	 * @param plan
	 *            - the admission plan from DB
	 * 
	 * @throws DaoTechnicalException
	 */
	@Override
	public void acceptStudents(long facultyId, int plan) throws DaoTechnicalException {
		try {
			connectionPool = ConnectionPool.getInstance();
		} catch (ConnectionExceptions e) {
			LOGGER.error(e.getMessage());
			throw new DaoTechnicalException(ErrorMessages.CAN_NOT_GET_ACCESS_TO_CONNECTION_POOL, e);

		}
		Connection connection;
		try {
			connection = connectionPool.getConnection();
		} catch (ConnectionExceptions ex) {
			LOGGER.error(ex.getMessage());
			throw new DaoTechnicalException(ErrorMessages.CAN_NOT_GET_CONNECTION, ex);
		}

		LOGGER.debug(LoggerMessages.RECEIVE_CONNECTION);

		PreparedStatement statement = null;

		try {
			statement = connection.prepareStatement(SQLQueries.ACCEPT_STUDENT);

			LOGGER.debug(LoggerMessages.TRYING_EXECUTE_QUERY);

			statement.setLong(1, facultyId);
			statement.setInt(2, plan);

			try {
				statement.executeUpdate();

				LOGGER.debug(LoggerMessages.EXECUTE_QUERY);

			} catch (SQLException e) {
				LOGGER.error(e.getMessage());
				throw new DaoTechnicalException(ErrorMessages.CAN_NOT_EXECUTE_QUERY, e);
			}
		} catch (SQLException e) {
			LOGGER.error(e.getMessage());
			throw new DaoTechnicalException(ErrorMessages.CAN_NOT_CREATE_STATEMENT, e);
		} finally {

			if (statement != null) {
				try {
					statement.close();
				} catch (SQLException e) {
					LOGGER.error(e.getMessage());
					throw new DaoTechnicalException(ErrorMessages.CAN_NOT_CLOSE_STATEMENT, e);
				}
			}
			connectionPool.releaseConnection(connection);
		}

	}

	/**
	 * Creates the list of students that entered to the University.
	 * 
	 * @return list of accepted students
	 * 
	 * 
	 * @throws DaoTechnicalException
	 */
	@Override
	public List<Student> getAcceptedStudents() throws DaoTechnicalException {
		try {
			connectionPool = ConnectionPool.getInstance();
		} catch (ConnectionExceptions e) {
			LOGGER.error(e.getMessage());
			throw new DaoTechnicalException(ErrorMessages.CAN_NOT_GET_ACCESS_TO_CONNECTION_POOL, e);

		}
		Connection connection;
		try {
			connection = connectionPool.getConnection();
		} catch (ConnectionExceptions ex) {
			LOGGER.error(ex.getMessage());
			throw new DaoTechnicalException(ErrorMessages.CAN_NOT_GET_CONNECTION, ex);
		}

		LOGGER.debug(LoggerMessages.RECEIVE_CONNECTION);

		PreparedStatement statement = null;
		ResultSet resultSet = null;
		List<Student> acceptedStudentsList;
		Student student = null;
		Faculty faculty = null;

		acceptedStudentsList = new ArrayList<Student>();

		try {
			statement = connection.prepareStatement(SQLQueries.GET_ACCEPTED_STUDENTS);

			LOGGER.debug(LoggerMessages.TRYING_EXECUTE_QUERY);

			try {
				resultSet = statement.executeQuery();

				LOGGER.debug(LoggerMessages.EXECUTE_QUERY);

				while (resultSet.next()) {
					try {
						String name = resultSet.getString(NAME);
						int totalScore = resultSet.getInt(TOTAL_SCORE);
						String facultyName = resultSet.getString(FACULTY_NAME);

						student = new Student();
						faculty = new Faculty();

						faculty.setFacultyName(facultyName);
						student.setName(name);
						student.setTotalScore(totalScore);
						student.setFaculty(faculty);
						acceptedStudentsList.add(student);
					} catch (SQLException e) {
						LOGGER.error(e.getMessage());
						throw new DaoTechnicalException(ErrorMessages.CAN_NOT_GET_CELL_DATA, e);
					}
				}

				LOGGER.debug(CREATED_LIST_OF_ACCEPTED_STUDENTS);

			} catch (SQLException e) {
				LOGGER.error(e.getMessage());
				throw new DaoTechnicalException(ErrorMessages.CAN_NOT_EXECUTE_QUERY, e);
			}
		} catch (SQLException e) {
			LOGGER.error(e.getMessage());
			throw new DaoTechnicalException(ErrorMessages.CAN_NOT_CREATE_STATEMENT, e);
		} finally {
			if (resultSet != null)
				try {
					resultSet.close();
				} catch (SQLException e) {
					LOGGER.error(e.getMessage());
					throw new DaoTechnicalException(ErrorMessages.CAN_NOT_CLOSE_RESULT_SET, e);
				}

			if (statement != null) {
				try {
					statement.close();
				} catch (SQLException e) {
					LOGGER.error(e.getMessage());
					throw new DaoTechnicalException(ErrorMessages.CAN_NOT_CLOSE_STATEMENT, e);
				}
			}
			connectionPool.releaseConnection(connection);
		}

		return acceptedStudentsList;

	}

	/**
	 * Checks power of user(student or admin).
	 * 
	 * @param password
	 *            - input password
	 * @param login
	 *            - input login
	 * 
	 * @return power
	 * 
	 * @throws DaoTechnicalException
	 */
	@Override
	public String checkForPower(String login, String password) throws DaoTechnicalException {
		try {
			connectionPool = ConnectionPool.getInstance();
		} catch (ConnectionExceptions e) {
			LOGGER.error(e.getMessage());
			throw new DaoTechnicalException(ErrorMessages.CAN_NOT_GET_ACCESS_TO_CONNECTION_POOL, e);

		}
		Connection connection;
		try {
			connection = connectionPool.getConnection();
		} catch (ConnectionExceptions ex) {
			LOGGER.error(ex.getMessage());
			throw new DaoTechnicalException(ErrorMessages.CAN_NOT_GET_CONNECTION, ex);
		}

		LOGGER.debug(LoggerMessages.RECEIVE_CONNECTION);

		PreparedStatement statement = null;
		ResultSet resultSet = null;
		String power = null;
		try {
			statement = connection.prepareStatement(SQLQueries.CHECK_FOR_ADMIN);

			LOGGER.debug(LoggerMessages.TRYING_EXECUTE_QUERY);

			statement.setString(1, login);

			try {
				resultSet = statement.executeQuery();

				LOGGER.debug(LoggerMessages.EXECUTE_QUERY);

				while (resultSet.next()) {
					try {
						String dbPassword = resultSet.getString(PASSWORD);
						if (dbPassword.equals(generateMD5(password))) {
							power = resultSet.getString(POWER);
						}

						LOGGER.debug(POWER_OF_USERS_IS + power);

					} catch (SQLException e) {
						LOGGER.error(e.getMessage());
						throw new DaoTechnicalException(ErrorMessages.CAN_NOT_GET_CELL_DATA, e);
					}
				}

			} catch (SQLException e) {
				LOGGER.error(e.getMessage());
				throw new DaoTechnicalException(ErrorMessages.CAN_NOT_EXECUTE_QUERY, e);
			}
		} catch (SQLException e) {
			LOGGER.error(e.getMessage());
			throw new DaoTechnicalException(ErrorMessages.CAN_NOT_CREATE_STATEMENT, e);
		} finally {
			if (resultSet != null)
				try {
					resultSet.close();
				} catch (SQLException e) {
					LOGGER.error(e.getMessage());
					throw new DaoTechnicalException(ErrorMessages.CAN_NOT_CLOSE_RESULT_SET, e);
				}
			if (statement != null) {
				try {
					statement.close();
				} catch (SQLException e) {
					LOGGER.error(e.getMessage());
					throw new DaoTechnicalException(ErrorMessages.CAN_NOT_CLOSE_STATEMENT, e);
				}
			}
			connectionPool.releaseConnection(connection);
		}

		return power;
	}

	/**
	 * Generate hashcode of password.
	 * 
	 * @param password
	 *            - password to generate
	 * @return md5 hash code
	 */

	private String generateMD5(String password) {
		java.security.MessageDigest md = null;
		try {
			md = MessageDigest.getInstance(MD5);
		} catch (NoSuchAlgorithmException e) {
			e.printStackTrace();
		}
		byte[] bytesOfPassword = null;
		try {
			bytesOfPassword = password.getBytes(ENCODING);
		} catch (UnsupportedEncodingException e) {
			e.printStackTrace();
		}
		byte[] hashedByteArray = md.digest(bytesOfPassword);
		StringBuilder sb = new StringBuilder();
		for (byte aHashedByteArray : hashedByteArray) {
			sb.append(Integer.toHexString((aHashedByteArray & 0xFF) | 0x100).substring(1, 3));
		}
		return sb.toString();

	}

}
